﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[_Old_ViewEntityAttributes]
    @EntityType varchar(max),
	@EntitySubType varchar(max) = NULL

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

DECLARE @strcols AS NVARCHAR(MAX), @numcols AS NVARCHAR(MAX), @datecols AS NVARCHAR(MAX), @bitcols AS NVARCHAR(MAX), @bincols AS NVARCHAR(MAX),
@query as NVARCHAR(MAX), @myformat as nvarchar(max)

  	select @strcols = STUFF((SELECT  ',' + QUOTENAME(AttributeName) 
						from DefinedAttributes WHERE EntityType = @EntityType AND (EntitySubType = @EntitySubType OR EntitySubType is null)
						AND DataType = 'String'
						group by  AttributeName 
				FOR XML PATH(''), TYPE
				).value('.', 'NVARCHAR(MAX)') 
			,1,1,'')
	select @numcols = STUFF((SELECT  ',' + QUOTENAME(AttributeName) 
						from DefinedAttributes WHERE EntityType = @EntityType AND (EntitySubType = @EntitySubType OR EntitySubType is null)
						AND DataType = 'Number'
						group by  AttributeName 
				FOR XML PATH(''), TYPE
				).value('.', 'NVARCHAR(MAX)') 
			,1,1,'')
	select @datecols = STUFF((SELECT  ',' + QUOTENAME(AttributeName) 
						from DefinedAttributes WHERE EntityType = @EntityType AND (EntitySubType = @EntitySubType OR EntitySubType is null)
						AND DataType = 'Date'
						group by  AttributeName 
				FOR XML PATH(''), TYPE
				).value('.', 'NVARCHAR(MAX)') 
			,1,1,'')
	select @bitcols = STUFF((SELECT  ',' + QUOTENAME(AttributeName) 
						from DefinedAttributes WHERE EntityType = @EntityType AND (EntitySubType = @EntitySubType OR EntitySubType is null)
						AND DataType = 'Bit'
						group by  AttributeName 
				FOR XML PATH(''), TYPE
				).value('.', 'NVARCHAR(MAX)') 
			,1,1,'')
	select @bincols = STUFF((SELECT  ',' + QUOTENAME(AttributeName) 
						from DefinedAttributes WHERE EntityType = @EntityType AND (EntitySubType = @EntitySubType OR EntitySubType is null)
						AND DataType = 'Binary'
						group by  AttributeName 
				FOR XML PATH(''), TYPE
				).value('.', 'NVARCHAR(MAX)') 
			,1,1,'')

	IF @EntitySubType is null
	BEGIN
		set @query = 'SELECT * FROM (SELECT DISTINCT EntityID from Entity WHERE EntityType = ''' + @EntityType + ''') AS mainatt'
	END
	ELSE
	BEGIN
		set @query = 'SELECT * FROM (SELECT DISTINCT EntityID from Entity WHERE EntityType = ''' + @EntityType + ''' AND EntitySubType = ''' + @EntitySubType + ''') AS mainatt'
	END

	IF @strcols IS NOT NULL
	BEGIN
		set @query = @query +
				' LEFT OUTER JOIN (SELECT EntityID as EID1, ' + @strcols + '
					from AttributesWithAttributeNames
				pivot 
				(
					max(AttributeValueString)
					for AttributeName in (' + @strcols + ')
				) a WHERE DataType=''' + 'String' + ''') AS stratt ON mainatt.EntityID = stratt.EID1'
	END
	IF @numcols IS NOT NULL
	BEGIN
		set @query = @query +
				' LEFT OUTER JOIN (SELECT EntityID as EID2, ' + @numcols + '
					from AttributesWithAttributeNames
				pivot 
				(
					max(AttributeValueNumber)
					for AttributeName in (' + @numcols + ')
				) a WHERE DataType=''' + 'Number' + ''') AS numatt ON mainatt.EntityID = numatt.EID2'
	END

	IF @datecols IS NOT NULL
	BEGIN
		set @query = @query +
				' LEFT OUTER JOIN (SELECT EntityID as EID3, ' + @datecols + '
					from AttributesWithAttributeNames
				pivot 
				(
					max(AttributeValueDateTime)
					for AttributeName in (' + @datecols + ')
				) a WHERE DataType=''' + 'Date' + ''') AS numatt ON mainatt.EntityID = numatt.EID3'
	END
	IF @bitcols IS NOT NULL
	BEGIN
		set @query = @query +
				' LEFT OUTER JOIN (SELECT EntityID as EID4, ' + @bitcols + '
					from AttributesWithAttributeNames
				pivot 
				(
					max(AttributeValueBit)
					for AttributeName in (' + @bitcols + ')
				) a WHERE DataType=''' + 'Bit' + ''') AS numatt ON mainatt.EntityID = numatt.EID4'
	END
	IF @bincols IS NOT NULL
	BEGIN
		set @query = @query +
				' LEFT OUTER JOIN (SELECT EntityID as EID5, ' + @bincols + '
					from AttributesWithAttributeNames
				pivot 
				(
					max(AttributeValueDateTime)
					for AttributeName in (' + @bincols + ')
				) a WHERE DataType=''' + 'Binary' + ''') AS numatt ON mainatt.EntityID = numatt.EID5'
	END
    
	execute(@query)   
END
